from contextlib import contextmanager
import datetime
from sqlalchemy import create_engine, orm, select
from sqlalchemy.exc import SQLAlchemyError
from model import *
# 初始化数据库引擎和Session
engine = create_engine('sqlite:///mydatabase.db', echo=True)
Session = orm.sessionmaker(bind=engine)

# 利用python上下文管理器和装饰器来做session相关的管理？
# 上下文管理器
@contextmanager
def db_session():
    session = Session()
    try:
        yield session
        session.commit()
    except SQLAlchemyError as e:
        print(f'{e},执行数据库时发生错误')
        session.rollback()
    finally:
        session.close()

class UserService():
    def __init__(self):
        self.session = Session()

    def addUser(self, username, password, email, role="user"):
        user = User(username=username, password=password, email=email, role=role)
        self.session.add(user)
        self.session.commit()

    def delUser(self, username):
        self.session.query(User).filter(User.username == username).delete()
        self.session.commit()

    def getUser(self, username):
        user = self.session.query(User).filter_by(username=username).first()
        return user
    
    def getUserById(self, id):
        user = self.session.query(User).filter_by(id=id).first()
        return user
    
    def getUserList(self, username=None):
        if username:
            user_list = self.session.query(User).filter_by(username=username).all()
        else:
            user_list = self.session.query(User).all()
        return user_list
    
class BookService():
    def __init__(self):
        self.session = Session()

    def addBook(self, title, isbn, author, count):
        book = Book(title, isbn, author, count)
        self.session.add(book)
        self.session.commit()

    def delBook(self,id):
        self.session.query(Book).filter(Book.id == id).delete()
        self.session.commit()

    def getBookById(self,id):
        book = self.session.query(Book).filter_by(id=id).first()
        return book    

    def getBookList(self, title=None):
        if title:
            bookList = self.session.query(Book).filter_by(title=title).all()
        else:
            bookList = self.session.query(Book).all()
        return bookList
    
    def getBookCount(self):
        return self.session.query(Book).count()
    
    def getBookCountById(self, book_id):
        # filter_by不需要跟类名: id = book_id， filter需要类名:Book.id == book_id
        return self.session.query(Book).filter_by(id=book_id).one()

    def deductBookCountById(self, book_id):
        book = self.session.query(Book).filter_by(id=book_id).one()
        book.count -= 1
        self.session.commit()

    def increaseBookCountById(self, book_id):
        book = self.session.query(Book).filter_by(id=book_id).one()
        book.count += 1
        self.session.commit()

class BorrowService():
    def __init__(self):
        self.session = Session()

    def all(self):
        list = self.session.query(Borrow).all()
        return list

    def borrowBook(self, user_id, book_id):
        bs = BookService()
        bs.deductBookCountById(book_id)
        book = Borrow(user_id, book_id, datetime.datetime.now())
        self.session.add(book)
        self.session.commit()

    def returnBook(self, user_id, book_id):
        stmt = select(Borrow).where(Borrow.user_id == user_id).where(Borrow.book_id == book_id).where(Borrow.return_time == None)
        borrow = self.session.scalars(stmt).one()
        borrow.return_time = datetime.datetime.now()
        self.session.commit()
        bs = BookService()
        bs.increaseBookCountById(book_id)
        

class LMSService():
    def __init__(self):
        self.session = Session()

    def login(self, name, password):
        us = UserService()
        user = us.getUser(name)
        if user:
            if user.password == password:
                return True, user.role, user.id
            else:
                return False, None, None
        else:
            return False, None, None
        
    def logout(self):
        return True

    def getLmsCount(self):
        return self.session.query(LMS).count()

    def getName(self):
        lms = self.session.query(LMS).first()
        return lms.name
    
    def addLms(self, name):
        lms = LMS(name)
        self.session.add(lms)
        self.session.commit()